# -*- coding: utf-8 -*-
"""
@Time ： 2022/12/29 21:52
@Author ： PJC
"""

import pymysql
from common.read_config import Environ
from common.read_yaml import get_yaml

data = get_yaml(Environ)
db = data['db']


def get_db_conn():
    conn = pymysql.connect(
        host=db['host'],
        port=db['port'],
        user=db['user_name'],
        passwd=db['password'],
        database=db['db_name'],
        charset='utf8'
    )
    return conn


def query_db(sql):
    conn = get_db_conn()  # 建立链接
    # cru = conn.cursor()
    cur = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 建立游标，读取数据转换成字典格式
    cur.execute(sql)  # 执行sql
    conn.commit()
    result = cur.fetchall()  # 获取查询结果
    print(result)
    cur.close()  # 关闭游标
    conn.close()  # 关闭链接
    return result


def change_db(sql):
    conn = get_db_conn()  # 建立链接
    cur = conn.cursor()  # 创建游标
    try:
        cur.execute(sql)  # 执行sql语句
        conn.commit()  # 提交更改
    except Exception as e:
        conn.rollback()  # 回滚
    finally:
        cur.close()  # 关闭游标
        conn.close()  # 关闭链接


def delete_db(sql):
    conn = get_db_conn()  # 建立链接
    cur = conn.cursor()  # 创建游标
    try:
        cur.execute(sql)  # 执行sql语句
        conn.commit()  # 提交更改
    except Exception as e:
        conn.rollback()  # 回滚
    finally:
        cur.close()  # 关闭游标
        conn.close()  # 关闭链接


def check_sql(del_keyword=None, table=None, matching_keyword=None, matching_condition=None):
    # 注意sql中''号嵌套的问题
    sql = "SELECT {} FROM {} WHERE {}='{}'".format(del_keyword, table, matching_keyword, matching_condition)
    # sql = "SELECT * FROM test_cloud_user.member_controller.yml WHERE mobile = '18668242746';"
    result = query_db(sql)
    return result


# 删除
def delete_sql(table=None, matching_keyword=None, matching_condition=None):
    # 注意sql中''号嵌套的问题
    sql = "DELETE FROM {} WHERE {}='{}'".format(table, matching_keyword, matching_condition)
    result = delete_db(sql)
    return result


if __name__ == '__main__':
    # query_db('show databases')
    query_db('select * from course')
    # check_sql('name', 'Api', 'id', 1)
